﻿use master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ProjectG3SemIV')
DROP DATABASE ProjectG3SemIV
GO
create database ProjectG3SemIV
GO
use ProjectG3SemIV
GO
create table [Role]
(
	RoleID int primary key identity,
	RoleName varchar(50) not null
)
GO
insert into [Role](RoleName) values('Admin')
insert into [Role](RoleName) values('Student')

select * from [Role]
GO
--Bang Account
create table Account
(
	AccountID int primary key identity(1,1),
	FullName nvarchar(50),
	AccountName varchar(50) not null unique,
	[Password] varchar(50) not null,
	Age int,
	Gender varchar(20),
	[Address] nvarchar(300),
	PhoneNumber varchar(12),
	Email varchar(100),
	DateOfBirth datetime,
	CreatedDate datetime default getdate(),
	RoleID int foreign key references [Role](RoleID)
)
GO
insert into Account(FullName,AccountName,[Password],Age,Gender,[Address],PhoneNumber,Email,DateOfBirth,RoleID) 
values(N'Lương Khắc Hải','luongkhachai','123',20,'male',N'Từ Liêm,Hà Nội','0123456789','luongkhachai@gmail.com','08/11/1993',2)

select * from Account
GO
create table Administrator
(
	AdminID int primary key identity,
	FullName nvarchar(50) not null,
	AdminName varchar(50) not null unique,
	[Password] varchar(50) not null,
	Email varchar(100),
	RoleID int foreign key references [Role](RoleID)
)
GO
insert into Administrator(FullName,AdminName,[Password],Email,RoleID) 
values(N'Lương Khắc Hải','hailk_c00639','123456','hailk_c00639@fpt.aptech.ac.vn',1)

select * from Administrator
GO
-- Bang loai(linh vuc) su kien
create table Field_Event
(
	FieldID int primary key identity(1,1),
	FieldName varchar(100) not null,
	[Description] varchar(250) null,
	Images varchar(200) null
)
GO
insert into Field_Event(FieldName,[Description],Images) values('Engineering', null, null)
insert into Field_Event(FieldName,[Description],Images) values('Pharmaceutical Sciences', null, null)
insert into Field_Event(FieldName,[Description],Images) values('Literature and History', null, null)
insert into Field_Event(FieldName,[Description],Images) values('Life Science', null, null)
insert into Field_Event(FieldName,[Description],Images) values('Information Technology', null, null)

select * from Field_Event
GO
--Bang su kien (luu thong tin chi tiet ve su kien)
create table [Event]
(
	EventID int primary key identity(1,1),
	EventName varchar(256) not null,
	FieldID int foreign key references Field_Event(FieldID),
	CreatedDate datetime default getdate(),
	StartDate datetime not null,  --ngay bat dau su kien
	FinishDate datetime not null, --ngay ket thuc su kien
	[Address] varchar(300) not null,  --dia diem to chuc
	SpeakerName varchar(50),		--Ten nguoi thuyet trinh
	ImageUrl varchar(200) not null,
	[Description] varchar(MAX) not null,
	Fee float not null,	--Le phi tham gia
	EventStatus varchar(50) not null, -- 0: Upcoming, 1: Conducted, 2: Finish
)
GO
insert into [Event](EventName,FieldID,StartDate,FinishDate,[Address],SpeakerName,ImageUrl,[Description],Fee,EventStatus) 
values('abc',1,'11/13/2013','11/15/2013','Ha Noi','Nguyen Tuan','imageURL','bcsnfjg',100,'Upcoming')
select * from [Event]
GO
--Bang dang ki tham gia du kien
create table EnrollEvent
(
	EnrollEventID int primary key identity(1,1),
	EventID int foreign key references [Event](EventID),
	AccountID int foreign key references Account(AccountID),
	IsWinner tinyint default(2) --1: True, 2:False
)
select * from EnrollEvent
GO
--insert into EnrollEvent(EventID,AccountID,IsWinner) values()
GO
-------------------CREATE PROCEDURE---------------------------
GO
---------------CREATE PROC CheckLogin---------------
create proc sp_CheckLoginAdmin
	@AdminName varchar(50),
	@Password varchar(50)
as
begin
	select [Role].RoleName from Administrator INNER JOIN [Role] on Administrator.RoleID = [Role].RoleID 
	where AdminName = @AdminName and [Password] = @Password

	--select [Role].RoleName from Administrator INNER JOIN [Role] on Administrator.RoleID = [Role].RoleID 
	--where AdminName = 'hailk_c00639' and [Password] = '123456'
end
GO
-------------------CREATE PROCEDURE ROLE----------------------
create proc sp_GetAllRole
as
begin
	select * from [Role] where RoleName not like 'Admin'
end
GO
create proc sp_GetRoleByID
	@RoleID int
as
begin
	select RoleName from [Role] where RoleID = @RoleID
end
GO
--------------------------CREATE PROCEDURE ACCOUNT--------------------------
create proc sp_GetAllAccount
as
begin
	select Account.AccountID,Account.FullName, Account.AccountName, Account.[Password], Account.Age, Account.Gender, Account.[Address], 
	Account.PhoneNumber, Account.Email, Account.DateOfBirth, Account.CreatedDate, [Role].RoleName as RoleName from Account 
	INNER JOIN [Role] on Account.RoleID = [Role].RoleID where [Role].RoleName not like 'Admin'
end
GO
create proc sp_GetAccountByID
	@AccountID int
as
begin
	select Account.FullName, Account.AccountName, Account.[Password], Account.Age, Account.Gender, Account.[Address], Account.PhoneNumber, 
	Account.Email, Account.DateOfBirth, Account.CreatedDate, [Role].RoleName as RoleName from Account 
	INNER JOIN [Role] on Account.RoleID = [Role].RoleID where AccountID = @AccountID
end
GO
create proc sp_GetAccountByName
	@AccountName varchar(50)
as
begin
	select Account.AccountID, Account.FullName, Account.[Password], Account.Age, Account.Gender, Account.[Address], Account.PhoneNumber, 
	Account.Email, Account.DateOfBirth, Account.CreatedDate, [Role].RoleName as RoleName from Account 
	INNER JOIN [Role] on Account.RoleID = [Role].RoleID where AccountName = @AccountName
end
--AccountID int primary key identity(1,1),
--	FullName nvarchar(50),
--	AccountName varchar(50) not null unique,
--	[Password] varchar(50) not null,
--	Age int,
--	Gender varchar(20),
--	[Address] nvarchar(300),
--	PhoneNumber varchar(12),
--	Email varchar(100),
--	DateOfBirth datetime,
--	CreatedDate datetime default getdate(),
--	RoleID int foreign key references [Role](RoleID)
GO
create proc sp_InsertAccount
	@FullName nvarchar(50),
	@AccountName varchar(50),
	@[Password] varchar(50),
	@Age int,
	@Gender varchar(20),
	@[Address] nvarchar(300),
	@PhoneNumber varchar(12),
	@Email varchar(100),
	@DateOfBirth datetime,
	@RoleID int
as
begin
	insert into Account(FullName,AccountName,[Password],Age,Gender,[Address],PhoneNumber,Email,DateOfBirth,RoleID) 
	values(@FullName,@AccountName,@[Password],@Age,@Gender,@[Address],@PhoneNumber,@Email,@DateOfBirth,@RoleID)
end
GO
create proc sp_UpdateAccount
	@AccountID int,
	@FullName nvarchar(50),
	@AccountName varchar(50),
	@[Password] varchar(50),
	@Age int,
	@Gender varchar(20),
	@[Address] nvarchar(300),
	@PhoneNumber varchar(12),
	@Email varchar(100),
	@DateOfBirth datetime,
	@RoleID int
as
begin
	update Account set
	FullName = @FullName,
	AccountName = @AccountName,
	[Password] = @[Password],
	Age = @Age,
	Gender = @Gender,
	[Address] = @[Address],
	PhoneNumber = @PhoneNumber,
	Email = @Email,
	DateOfBirth = @DateOfBirth,
	RoleID = @RoleID
	where AccountID = @AccountID
end
GO
create proc sp_DeleteAccountByID
	@AccountID int
as
begin
	delete from Account where AccountID = @AccountID
end
GO
create proc sp_SearchAccountByName
	@AccountName varchar(50)
as
begin
	select * from Account where AccountName like '%' + @AccountName + '%'
end
GO
---------------------CREATE PROCEDURE ADMINISTRATOR-------------------
	--AdminID int primary key identity,
	--FullName nvarchar(50) not null,
	--AdminName varchar(50) not null unique,
	--[Password] varchar(50) not null,
	--Email varchar(100),
	--RoleID int foreign key references [Role](RoleID)
create proc sp_GetAllAdministrator
as
begin
	select Administrator.AdminID, Administrator.FullName, Administrator.AdminName, Administrator.[Password], Administrator.Email, 
	[Role].RoleName from Administrator INNER JOIN [Role] on Administrator.RoleID = [Role].RoleID where [Role].RoleName like 'Admin'
end
drop proc sp_GetAllAdministrator
GO
create proc sp_GetAdministratorByName
as
begin
	select AdminName from Administrator INNER JOIN [Role] on Administrator.RoleID = [Role].RoleID where [Role].RoleName like 'Admin'
end
GO
---------------------CREATE PROCEDURE FIELD_EVENT------------------
	--FieldID int primary key identity(1,1),
	--FieldName varchar(100) not null,
	--[Description] varchar(250) null,
	--Images varchar(200) null
GO
-- Da tao procedure sp_GetAllFieldEvent
create proc sp_GetAllFieldEvent
as
begin
	select * from Field_Event
end
GO
create proc sp_GetFieldEventByID
	@FieldID int
as
begin
	select FieldName,[Description],Images from Field_Event where FieldID = @FieldID
end
GO
create proc sp_GetFieldEventName
	@FieldName varchar(100)
as
begin
	select FieldID,[Description],Images from Field_Event where FieldName = @FieldName
end
GO
create proc sp_InsertFieldEvent
	@FieldName varchar(100),
	@[Description] varchar(250),
	@Images varchar(200)
as
begin
	insert into Field_Event(FieldName,[Description],Images) values(@FieldName,@[Description],@Images)
end
GO
create proc sp_UpdateFieldEvent
	@FieldID int,
	@FieldName varchar(100),
	@[Description] varchar(250),
	@Images varchar(200)
as
begin
	update Field_Event set
	FieldName = @FieldName,
	[Description] = @[Description],
	Images = @Images,
	where FieldID = @FieldID
end
GO
create proc sp_DeleteFieldEventByID
	@FieldID int
as
begin
	delete from Field_Event where FieldID = @FieldID
end
GO
create proc sp_DeleteFieldEventByName
	@FieldName varchar(100)
as
begin
	delete from Field_Event where FieldName = @FieldName
end
GO
create proc sp_SearchFieldEventByName
	@FieldName varchar(100)
as
begin
	select * from Field_Event where FieldName like '%' + @FieldName + '%'
end
GO
--------------CREATE PROCEDURE EVENT---------------------
	--EventID int primary key identity(1,1),
	--EventName varchar(256) not null,
	--FieldID int foreign key references Field_Event(FieldID),
	--CreatedDate datetime default getdate(),
	--StartDate datetime not null,  --ngay bat dau su kien
	--FinishDate datetime not null, --ngay ket thuc su kien
	--[Address] varchar(300) not null,  --dia diem to chuc
	--SpeakerName varchar(50),		--Ten nguoi thuyet trinh
	--ImageUrl varchar(200) not null,
	--[Description] varchar(MAX) not null,
	--Fee float not null,	--Le phi tham gia
	--EventStatus tinyint not null
GO
-- Da tao store procedure sp_GetAllEvent
create proc sp_GetAllEvent
as
begin
	select [Event].EventID,[Event].EventName,Field_Event.FieldName as FieldName,Field_Event.FieldID as FieldID,[Event].CreatedDate,
	[Event].StartDate,[Event].FinishDate, [Event].[Address],[Event].SpeakerName,[Event].ImageUrl,[Event].[Description],[Event].Fee,
	[Event].EventStatus from [Event] INNER JOIN Field_Event on [Event].FieldID = Field_Event.FieldID
end
exec sp_GetAllEvent
DROP PROCEDURE sp_GetAllEvent
GO
create proc sp_GetEventByName
	@EventName varchar(256)
as
begin
	select [Event].EventID,[Event].EventName,Field_Event.FieldID,[Event].CreatedDate,[Event].StartDate,[Event].FinishDate,[Event].[Address],
	[Event].SpeakerName,[Event].ImageUrl,[Event].[Description],[Event].Fee,[Event].EventStatus from [Event] INNER JOIN Field_Event on 
	[Event].FieldID = Field_Event.FieldID where EventName = @EventName
end
GO
create proc sp_InsertEvent
	@EventName varchar(256),
	@FieldID int,
	@CreatedDate datetime,
	@StartDate datetime,
	@FinishDate datetime,
	@[Address] varchar(300),
	@SpeakerName varchar(50),
	@ImageUrl varchar(200),
	@[Description] varchar(MAX),
	@Fee float,
	@EventStatus tinyint
as
begin
	insert into [Event](EventName,FieldID,CreatedDate,StartDate,FinishDate,[Address],SpeakerName,ImageUrl,[Description],Fee,EventStatus) 
	values(@EventName,@FieldID,@CreatedDate,@StartDate,@FinishDate,@[Address],@SpeakerName,@ImageUrl,@[Description],@Fee,@EventStatus)
end
GO
create proc sp_UpdateEvent
	@EventID int,
	@EventName varchar(256),
	@FieldID int,
	@CreatedDate datetime,
	@StartDate datetime,
	@FinishDate datetime,
	@[Address] varchar(300),
	@SpeakerName varchar(50),
	@ImageUrl varchar(200),
	@[Description] varchar(MAX),
	@Fee float,
	@EventStatus tinyint
as
begin
	update [Event] set
	EventName = @EventName,
	FieldID = @FieldID,
	CreatedDate = @CreatedDate,
	StartDate = @StartDate,
	FinishDate = @FinishDate,
	[Address] = @[Address],
	SpeakerName = @SpeakerName,
	ImageUrl = @ImageUrl,
	[Description] = @[Description],
	Fee = @Fee,
	EventStatus = @EventStatus
	where EventID = @EventID
end
GO
create proc sp_DeleteEvent
	@EventID int
as
begin
	delete from [Event] where EventID = @EventID
end
GO
create proc sp_SearchEventByName
	@EventName varchar(256)
as
begin
	select * from [Event] where EventName like '%'+@EventName+'%'
end
GO
----------------CREATE PROCEDURE ENROLL EVENT------------------

	--EnrollEventID int primary key identity(1,1),
	--EventID int foreign key references [Event](EventID),
	--AccountID int foreign key references Account(AccountID),
	--IsWinner tinyint default(2) --1: True, 2:False
GO
create proc sp_GetAllEnrollEvent
as
begin
	select * from EnrollEvent
end
GO
create proc sp_InsertEnrollEvent
	@EventID int,
	@AccountID int,
	@IsWinner tinyint
as
begin
	insert into EnrollEvent(EventID,AccountID,IsWinner) values(@EventID,@AccountID,@IsWinner)
end
GO
create proc sp_UpdateEnrollEvent
	@EnrollEventID int,
	@EventID int,
	@AccountID int,
	@IsWinner tinyint
as
begin
	update EnrollEvent set EventID = @EventID, AccountID = @AccountID, IsWinner = @IsWinner where EnrollEventID = @EnrollEventID
end	
GO
create proc sp_DeleteEnrollEventByID
	@EnrollEventID int
as
begin
	delete from EnrollEvent where EnrollEventID = @EnrollEventID
end
GO
create proc sp_SearchEnrollEventByID
	@EnrollEventID int
as
begin
	select * from EnrollEvent where EnrollEventID = @EnrollEventID
end
GO
create proc sp_SearchEnrollEventByEventID
	@EventID int
as
begin
	select * from EnrollEvent where EventID = @EventID
end
GO
create proc sp_SearchEnrollEventByAccountID
	@AccountID int
as
begin
	select * from EnrollEvent where AccountID = @AccountID
end